package xju.rjxy.shop.mapper.impl;

import xju.rjxy.shop.mapper.UserGameMapper;
import xju.rjxy.shop.model.domain.UserGame;
import xju.rjxy.shop.model.vo.UserGameVO;
import xju.rjxy.shop.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.math.BigDecimal;
import java.util.List;

/**
 * 用户游戏关联数据访问接口实现类
 */
public class UserGameMapperImpl implements UserGameMapper {
    private final QueryRunner runner = new QueryRunner();

    /**
     * 查询用户游戏关联列表
     */
    @Override
    public List<UserGameVO> selectUserGameList() {
        // 修正表名t_user_game为user_games，并完善字段
        String sql = "SELECT " +
                "id, " +
                "user_id AS userId, " +
                "game_id AS gameId, " +
                "purchase_time AS purchaseTime, " +
                "purchase_price AS purchasePrice, " +
                "play_count AS playCount, " +
                "play_time AS playTime, " +
                "last_played AS lastPlayed, " +
                "flag, " +
                "status_code AS statusCode " +
                "FROM user_games " +
                "WHERE flag != 4"; // 过滤已封禁记录（flag=4）

        List<UserGameVO> userGameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            userGameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(UserGameVO.class)
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return userGameList;
    }
    
    /**
     * 根据用户ID查询用户游戏列表
     */
    @Override
    public List<UserGameVO> selectByUserId(Integer userId) {
        String sql = "SELECT " +
                "ug.id, " +
                "ug.user_id AS userId, " +
                "ug.game_id AS gameId, " +
                "ug.purchase_time AS purchaseTime, " +
                "ug.purchase_price AS purchasePrice, " +
                "ug.play_count AS playCount, " +
                "ug.play_time AS playTime, " +
                "ug.last_played AS lastPlayed, " +
                "ug.flag, " +
                "ug.status_code AS statusCode, " +
                "g.game_name AS gameName, " +
                "g.category AS gameCategory " +
                "FROM user_games ug " +
                "JOIN games g ON ug.game_id = g.id " +
                "WHERE ug.user_id = ? AND ug.flag != 4 AND g.deleted = 0 " +
                "ORDER BY ug.last_played DESC";

        List<UserGameVO> userGameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            userGameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(UserGameVO.class),
                    userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return userGameList;
    }
    
    /**
     * 根据好友ID查询好友游戏列表
     */
    @Override
    public List<UserGameVO> selectByFriendId(Integer friendId) {
        String sql = "SELECT " +
                "ug.id, " +
                "ug.user_id AS userId, " +
                "ug.game_id AS gameId, " +
                "ug.purchase_time AS purchaseTime, " +
                "ug.purchase_price AS purchasePrice, " +
                "ug.play_count AS playCount, " +
                "ug.play_time AS playTime, " +
                "ug.last_played AS lastPlayed, " +
                "ug.flag, " +
                "ug.status_code AS statusCode, " +
                "g.game_name AS gameName, " +
                "g.category AS gameCategory " +
                "FROM user_games ug " +
                "JOIN games g ON ug.game_id = g.id " +
                "WHERE ug.user_id = ? AND ug.flag != 4 AND g.deleted = 0 " +
                "ORDER BY ug.last_played DESC";

        List<UserGameVO> userGameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            userGameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(UserGameVO.class),
                    friendId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return userGameList;
    }
    
    /**
     * 添加用户游戏关联
     */
    @Override
    public int insertUserGame(UserGame userGame) {
        String sql = "INSERT INTO user_games (user_id, game_id, purchase_time, purchase_price, " +
                "play_count, play_time, last_played, flag, status_code) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, 
                    userGame.getUserId(), 
                    userGame.getGameId(), 
                    userGame.getPurchaseTime(), 
                    userGame.getPurchasePrice(), 
                    userGame.getPlayCount(), 
                    userGame.getPlayTime(), 
                    userGame.getLastPlayed(), 
                    userGame.getFlag(), 
                    userGame.getStatusCode());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return result;
    }
    
    /**
     * 更新用户游戏信息
     */
    @Override
    public int updateUserGame(UserGame userGame) {
        String sql = "UPDATE user_games SET play_count = ?, play_time = ?, last_played = ?, " +
                "flag = ?, status_code = ? WHERE id = ?";
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, 
                    userGame.getPlayCount(), 
                    userGame.getPlayTime(), 
                    userGame.getLastPlayed(), 
                    userGame.getFlag(), 
                    userGame.getStatusCode(), 
                    userGame.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return result;
    }
    
    /**
     * 删除用户游戏关联
     */
    @Override
    public int deleteUserGame(Integer id) {
        String sql = "UPDATE user_games SET flag = 4 WHERE id = ?";
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return result;
    }
    
    /**
     * 根据用户ID和游戏ID查询
     */
    @Override
    public UserGame selectByUserIdAndGameId(Integer userId, Integer gameId) {
        String sql = "SELECT * FROM user_games WHERE user_id = ? AND game_id = ? AND flag != 4";
        UserGame userGame = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            userGame = runner.query(connection, sql, new BeanHandler<>(UserGame.class), userId, gameId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return userGame;
    }
    
    /**
     * 获取用户游戏时长统计
     */
    @Override
    public Integer getTotalPlayTime(Integer userId) {
        String sql = "SELECT COALESCE(SUM(play_time), 0) FROM user_games WHERE user_id = ? AND flag != 4";
        Integer totalPlayTime = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            Object result = runner.query(connection, sql, new ScalarHandler<>(), userId);
            if (result instanceof BigDecimal) {
                totalPlayTime = ((BigDecimal) result).intValue();
            } else if (result instanceof Number) {
                totalPlayTime = ((Number) result).intValue();
            } else {
                totalPlayTime = 0;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return totalPlayTime;
    }
    
    /**
     * 获取用户最近游玩游戏列表
     */
    @Override
    public List<UserGameVO> selectRecentPlayedGames(Integer userId, Integer limit) {
        String sql = "SELECT " +
                "ug.id, " +
                "ug.user_id AS userId, " +
                "ug.game_id AS gameId, " +
                "ug.purchase_time AS purchaseTime, " +
                "ug.purchase_price AS purchasePrice, " +
                "ug.play_count AS playCount, " +
                "ug.play_time AS playTime, " +
                "ug.last_played AS lastPlayed, " +
                "ug.flag, " +
                "ug.status_code AS statusCode, " +
                "g.game_name AS gameName, " +
                "g.pic AS pic, " +
                "g.score AS score, " +
                "g.category AS gameCategory " +
                "FROM user_games ug " +
                "JOIN games g ON ug.game_id = g.id " +
                "WHERE ug.user_id = ? AND ug.flag != 4 AND g.deleted = 0 " +
                "AND ug.last_played IS NOT NULL " +
                "ORDER BY ug.last_played DESC " +
                "LIMIT ?";

        List<UserGameVO> userGameList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            userGameList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(UserGameVO.class),
                    userId,
                    limit
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return userGameList;
    }
}